2023 |
您所在的位置:网站首页 › rac adg 部署 › 2023 |
19c ADG --原文:https://www.cnblogs.com/ss-33/p/14500517.html 10、主库设置为归档模式 alter database archivelog 设置主数据库为日志强制写状态 alter database force logging; 查看状态日志强制写状态为YES select log_mode,force_logging from v$database; LOG_MODE FOR ------------ --- ARCHIVELOG YES 11、查看主库数据库的日志组个数与大小,因为我们创建standby日志组的个数是原日志组个数+1再与thread的积,size不能小于原日志文件的大小。 SQL> select group#,THREAD#,bytes/1024/1024 from v$log; GROUP# THREAD# BYTES/1024/1024 ---------- ---------- --------------- 1 1 200 2 1 200 3 1 200 SQL> select member from v$logfile; MEMBER -------------------------------------- /u01/app/oradata/DT/redo03.log /u01/app/oradata/DT/redo02.log /u01/app/oradata/DT/redo01.log 12、创建standby日志组,个数是原日志组个数+1再与实例数的积,size不能小于原日志文件的大小 注:RAC环境注意实例有几个,新建standby日志组的路径可与原日志组相同。 SQL> alter database add standby logfile '/u01/app/oradata/DT/standby01.log' size 200m; Database altered. alter database add standby logfile '/u01/app/oradata/DT/standby01.log' size 200m; alter database add standby logfile '/u01/app/oradata/DT/standby02.log' size 200m; alter database add standby logfile '/u01/app/oradata/DT/standby03.log' size 200m; alter database add standby logfile '/u01/app/oradata/DT/standby04.log' size 200m; 创建完成后查询是否成功 SQL> select group#,status,type,member from v$logfile; GROUP# STATUS TYPE MEMBER ---------- ------- ------- -------------------------------------------------- 3 ONLINE /u01/app/oradata/DT/redo03.log 2 ONLINE /u01/app/oradata/DT/redo02.log 1 ONLINE /u01/app/oradata/DT/redo01.log 4 STANDBY /u01/app/oradata/DT/standby01.log 5 STANDBY /u01/app/oradata/DT/standby02.log 6 STANDBY /u01/app/oradata/DT/standby03.log 7 STANDBY /u01/app/oradata/DT/standby04.log 7 rows selected. 13、启动备库到NOMOUNT $ sqlplus / as sysdba SQL> startup nomount pfile='/home/oracle/backup/INITtestdb.ORA'; 注:如果pfile文件放在默认路径,且文件名正确的话。不需要指定pfile路径,直接startup nomount即可。 14、duplicate开始 在主库上通过rman进行复制备库(注意在这一步之前必须退出备库的所有连接,否则会报错) rman target sys/oracle auxiliary sys/oracle@dgtest rman> duplicate target database for standby nofilenamecheck from active database; 经资料查询,发现在duplicate传输时,windows下全部是大写字符,所以,还得修改 DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT参数的路径,全用大写 *************************错误信息************************* 19c测试时由于备库的环境变量ORACLE_HOME配置有误,导致始终无法连接到备库,报错如下: [oracle@test19c admin]$ rman target sys/oracle auxiliary sys/oracle@dgtest Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jun 17 18:13:10 2019 Version 19.2.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: DT (DBID=1254913786) connected to auxiliary database (not started) RMAN> exit 检查发现在.bash_profile文件中,ORACLE_BASE的路径最后加了一个/,导致在ORACLE_HOME中多了一个/ ORACLE_BASE=/u01/app/oracle/; export ORACLE_BASE ---/u01/app/oracle不该多那个/啊。。。 ORACLE_HOME=$ORACLE_BASE/product/19.2.0/dbhome_1; export ORACLE_HOME 最终使用echo $ORACLE_HOME时看到的路径如下: /u01/app/oracle//product/19.2.0/dbhome_1 但是最无奈的是你输入cd $ORACLE_HOME的时候却可以进入正确的路径,然后pwd会显示:/u01/app/oracle/product/19.2.0/dbhome_1 所以之前检查时,始终认为环境变量没有问题。最终将ORACLE_BASE修改为/u01/app/oracle 然后重启监听,重启备库就好了 总结:还是使用echo来检查各种环境变量,不要直接cd进去检查。 ------分割线------ 注意db_recovery_file_dest_size的大小,上次部署12c生产环境就是大小有问题,修改后解决: SQL> alter system set db_recovery_file_dest_size=60G; ***************************End*************************** 15、open备库,并恢复到自动恢复状态 确认备库状态: SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED 把备库启动到open only下面: SQL> alter database open read only; 在备库上启动数据库到恢复管理模式,并开始准备从主库接受归档日志的传输: SQL> alter database recover managed standby database using current logfile disconnect from session; *************************错误信息************************* 备库启动报错: SQL> alter database open read only; alter database open read only * ERROR at line 1: ORA-10458: standby database requires recovery ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/u01/app/oradata/dgtest/system01.dbf' 在主备库上查看alert日志,发现备库alert日志有如下信息: PR00 (PID:7481): Please verify that primary database is transporting redo logs to the standby database 根据信息检查,发现redo根本没过来,因为在备库归档目录没有看到一个归档文件!!! 目前定位就是无法传输归档,于是在主备库检查归档是否有报错: select status,error from v$archive_dest; 在主库发现报错: SQL> select status,error from v$archive_dest; STATUS ERROR --------- ----------------------------------------------------------------- VALID ERROR ORA-16086: Redo data cannot be written to the standby redo log 这种报错一般也是和配置文件相关,网上也有相关文档和排错思路。 经过多次测试发现是和db_unique_name参数没有配置有关: 检查发现备库的db_unique_name在没有配置的情况下默认也叫DT,和主库一致。 这就导致了主备库因为db_unique_name一致而无法区分,进而导致redo无法传输,也就没有归档了。 SQL> show parameter name NAME TYPE VALUE ---------------------------- ----------- ------------------------------ cdb_cluster_name string cell_offloadgroup_name string db_file_name_convert string /u01/app/oradata/DT, /u01/app/oradata/dgtest db_name string DT db_unique_name string DT global_names boolean FALSE instance_name string dgtest lock_name_space string log_file_name_convert string /u01/app/oradata/DT, /u01/app/oradata/dgtest pdb_file_name_convert string processor_group_name string service_names string DT SQL> 上面的信息可以看到instance_name是dgtest,但是db_unique_name还是DT,所以导致redo无法传输。 最后在备库上修改pfile文件,加上db_unique_name的配置就解决问题了。 最好是主备库都在pfile中定义一下db_unique_name! 本次部署环境,19c主库安装好后,生成的pfile没有定义db_unique_name,所以也就忽略了这个问题。 -------分割线,下面步骤操作有问题,应该先open库---------- 回到原主库启动STANDBY开始接收并恢复主库的日志 alter database recover managed standby database using current logfile disconnect from session; 在mount状态下输入上面语句,导致数据库无法open,所以需要退出这个状态 alter database recover managed standby database cancel; ***************************End*************************** 16、主库检查LNS进程: select process,status from v$managed_standby; 查看进程,看有没有LNS进程,如果没有则需要检查DG环境 SQL> select process,status from v$managed_standby; PROCESS STATUS --------- ------------ DGRD ALLOCATED ARCH CLOSING DGRD ALLOCATED ARCH OPENING ARCH CONNECTED ARCH CONNECTED LNS OPENING DGRD ALLOCATED LNS WRITING 9 rows selected. 17、检查主备库角色和其他信息 在备库端查看其角色是否已经是physical standby,检查当前备库的模式PROTECTION_MODE SQL> select DATABASE_ROLE,protection_mode,open_mode from v$database; DATABASE_ROLE PROTECTION_MODE OPEN_MODE ---------------- -------------------- -------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY 在主库查看其角色 SQL> select DATABASE_ROLE,open_mode from gv$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY READ WRITE 在备库查看data guard为哪种日志接受方式 SQL> select process,client_process,sequence#,status from v$managed_standby; PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 0 CONNECTED DGRD N/A 0 ALLOCATED DGRD N/A 0 ALLOCATED ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED ARCH ARCH 0 CONNECTED RFS Archival 0 IDLE RFS LGWR 31 IDLE RFS UNKNOWN 0 IDLE MRP0 N/A 31 APPLYING_LOG 10 rows selected. 18、检查主备库sequence# select max(sequence#) from v$archived_log; select sequence#,name,standby_dest,applied,deleted from v$archived_log where archived='YES' order by sequence# desc; 主库: SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 30 备库 SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 30 19、测试日志队列是否能正常传输 在备库查看日志的队列情况 SQL> select sequence#,applied,first_time,next_time from v$archived_log order by sequence#; SEQUENCE# APPLIED FIRST_TIM NEXT_TIME ---------- --------- --------- --------- 30 YES 18-JUN-19 18-JUN-19 在主库进行强制归档 ALTER SYSTEM ARCHIVE LOG CURRENT; alter system switch logfile; 在备库查看日志的队列情况,看新的归档日志有没有正常传输过来 SQL> select sequence#,applied,first_time,next_time from v$archived_log order by sequence#; SEQUENCE# APPLIED FIRST_TIM NEXT_TIME ---------- --------- --------- --------- 30 YES 18-JUN-19 18-JUN-19 31 NO 18-JUN-19 18-JUN-19 32 IN-MEMORY 18-JUN-19 18-JUN-19 检查下两边的日志同步情况 select sequence# from v$archived_log where applied='YES'; 看看有没有写的redo log: select sequence#,applied from v$archived_log; 20、查看DG是否正常工作,这一步主要看归档有无报错 select dest_id,error,status from v$archive_dest where status='ERROR'; SQL> select dest_id,error,status from v$archive_dest where status='ERROR'; no rows selected 也可以直接查看所有归档目录信息: select dest_id,error,status from v$archive_dest 21、在主库新建表空间、用户、表,并插入数据来测试备库是否能及时同步数据 主库创建测试表空间: SQL> create tablespace test datafile '/u01/app/oradata/DT/dt01.dbf' size 50m; 主备库查看数据文件状态 select FILE#,CREATION_TIME,STATUS,NAME,BYTES from v$datafile; 主库: SQL> select FILE#,CREATION_TIME,STATUS,NAME,BYTES from v$datafile; FILE# CREATION_ STATUS NAME BYTES ------- --------- ------- ------------------------------------------ ---------- 1 04-FEB-19 SYSTEM /u01/app/oradata/DT/system01.dbf 943718400 3 04-FEB-19 ONLINE /u01/app/oradata/DT/sysaux01.dbf 555745280 4 04-FEB-19 ONLINE /u01/app/oradata/DT/undotbs01.dbf 68157440 5 18-JUN-19 ONLINE /u01/app/oradata/DT/dt01.dbf 52428800 7 04-FEB-19 ONLINE /u01/app/oradata/DT/users01.dbf 5242880 备库: SQL> select FILE#,CREATION_TIME,STATUS,NAME,BYTES from v$datafile; FILE# CREATION_ STATUS NAME BYTES ------- --------- ------- ------------------------------------------ ---------- 1 04-FEB-19 SYSTEM /u01/app/oradata/dgtest/system01.dbf 943718400 3 04-FEB-19 ONLINE /u01/app/oradata/dgtest/sysaux01.dbf 555745280 4 04-FEB-19 ONLINE /u01/app/oradata/dgtest/undotbs01.dbf 68157440 5 18-JUN-19 ONLINE /u01/app/oradata/dgtest/dt01.dbf 52428800 7 04-FEB-19 ONLINE /u01/app/oradata/dgtest/users01.dbf 5242880 主库操作: 1> 创建用户 create user dgtest default tablespace test identified by oracle; grant dba to dgtest; 2> 切换用户 sqlplus dgtest/oracle 3> dgtest用户下创建表和插入测试数据 --创建表 create table dgtest ( id number(9) not null primary key, classname varchar2(40) not null ); --插入数据 insert into dgtest values(28,'class one'); insert into dgtest values(29,'detest one'); commit; 在备库执行查询: select * from dgtest.dgtest; SQL> select * from dgtest.dgtest; ID CLASSNAME ------ ---------------------------------------- 28 class one 29 detest one 删除测试数据: drop tablespace test including contents and datafiles; drop user dgtest cascade; 到此DG环境部署完毕,数据可以正常同步。 -------------------------------------------------------------- ------------------------主备库切换测试------------------------ -------------------------------------------------------------- #切换的两种方式switchover和failover switchover 切换:主库与备库数据同步正常情况下的切换,主要用于主备维护、切换演练等; failover 切换:主库与备库在数据未同步情况下的强制切换,主要用于主库宕机、故障情况下切换; 一、switchover Oracle 物理DG切换 在进行DATA GUARD的物理STANDBY切换前需要注意: 确认主库和备库间网络连接通畅; 确认没有活动的会话连接在数据库中; 确保STANDBY数据库处于ARCHIVELOG模式; 如果设置了REDO应用的延迟,那么将这个设置去掉; 确保配置了主库和备库的初始化参数,使得切换完成后,DATA GUARD机制可以顺利的运行。 #准备工作: 确认当前主库只有当前会话连接: set pages 100 linesize 1000; select SWITCHOVER_STATUS from v$database; SQL> select SWITCHOVER_STATUS from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY select count(*) from v$session where username is not null; SQL> select count(*) from v$session where username is not null; COUNT(*) ---------- 2 若有多于一个会话则: select sid,serial# from v$session where username is not null; SQL> select sid,serial# from v$session where username is not null; SID SERIAL# ----- ---------- 2 10746 621 13864 select userenv('sid') from dual; SQL> select userenv('sid') from dual; USERENV('SID') -------------- 621 则执行下面语句杀掉:alter system kill session 'sid,serial#'; SQL> alter system kill session '621,13864'; alter system kill session '621,13864' * ERROR at line 1: ORA-00027: cannot kill current session 原来621是当前会话,可以不用理会。 #正式切换: 主库与备库数据同步,且正常运行,将主库与备库的角色互换,也可以互换回来。 切换前,务必检查当前主库与备库的归档是否是同步的,确认同步后再执行切换 主库切备库 1、查看库的角色 主库: SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE; CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS ------- ---------------- ---------------- -------------------- CURRENT READ WRITE PRIMARY TO STANDBY SWITCHOVER_STATUS为TO STANDBY,说明主库可以切换为备库 备库: SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE; CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS ------- ---------------- ---------------- -------------------- STANDBY READ ONLY PHYSICAL STANDBY NOT ALLOWED SWITCHOVER_STATUS为NOT ALLOWED,这是备库的正常状态。 在主库首先切换后,我们再查询备库状态会发现SWITCHOVER_STATUS有改变 2、对主库进行切换 SQL> alter database commit to switchover to physical standby with session shutdown; 此时主库已经开始切换到备库的过程了,而备库的SWITCHOVER_STATUS已经变成TO PRIMARY 3、此时主库已经关闭,打到read only或同步状态 SQL> startup mount; ORACLE instance started. Total System Global Area 905967800 bytes Fixed Size 8902840 bytes Variable Size 788529152 bytes Database Buffers 100663296 bytes Redo Buffers 7872512 bytes Database mounted. SQL> alter database open read only; Database altered. 4、再查主库的角色,确认已经切换为备库 SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE; CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS ------- ---------------- ---------------- -------------------- STANDBY READ ONLY PHYSICAL STANDBY TO PRIMARY 注:这一步操作完成后,此时主备库都是open read only状态,且SWITCHOVER_STATUS都为TO PRIMARY。 此时已经没有真正主库存在的了,主库已经切换为备库,备库还未切换成主库。 也就是说此时主备库都能切换为真正的主库 5、在备库上启动数据库到恢复管理模式,并开始准备从主库接受归档日志的传输。 SQL> alter database recover managed standby database using current logfile disconnect from session; 注:这一步不执行的话,OPEN_MODE状态始终是READ ONLY,无法变成正常的READ ONLY WITH APPLY SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT; 查资料显示,12c之后使用上面这个语句来APPLY,黄色部分为和11gR2的区别 6、再查主库的角色,确认已经切换为备库,且OPEN_MODE为READ ONLY WITH APPLY SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE; CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS ------- -------------------- ---------------- -------------------- STANDBY READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY 备库切主库 1、查看备库角色: SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE; CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS ------- -------------------- ---------------- -------------------- STANDBY READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY SWITCHOVER_STATUS为TO PRIMARY,说明备库可以切换为主库 2、备切主命令 SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; 3、确认备库已经是mount状态 SQL> select status,instance_name from v$instance; STATUS INSTANCE_NAME ------------ ---------------- MOUNTED dgtest 4、打到OPEN状态 SQL> ALTER DATABASE OPEN; 现在的主库切换几次日志文件,备库可以同步日志文件。 注:只有在备库OPEN完成切换,成为新主库之后,原主库的SWITCHOVER_STATUS状态才会由TO PRIMARY变为NOT ALLOWED 5、再查备库的角色,确认已经切换为主库 SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE; CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS ------- ---------------- ---------------- -------------------- CURRENT READ WRITE PRIMARY TO STANDBY -------------------------------------------------------------- -----到此,主备库切换完毕,standby to primary过程已经完成----- -------------------------------------------------------------- 二、failover灾难切换 主库宕机,无法启动,紧急启用备库。直接在备库上操作,将备库转换为主库角色 备库上执行下面四条命令即可: SQL > alter database recover managed standby database finish; SQL > alter database commit to switchover to primary; SQL > shutdown immediate; SQL > startup; |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |